/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Login;

import DataAccess.DataOperations;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author anh anh
 */
public class DAL {

    Connection cnn;

    void openConnection() throws ClassNotFoundException, SQLException {
//        Class.forName("net.sourceforge.jtds.jdbc.Driver");
//        cnn = DriverManager.getConnection("jdbc:jtds:sqlserver://127.0.0.1:1433/MySweetHome;User=sa;password=fat123456");
        cnn = new DataOperations().getConnection();

    }

    public ResultSet executeQuery(String sqlQuerry) throws SQLException, ClassNotFoundException {
        if (cnn == null) {
            openConnection();
        }
        try {
            Statement st = cnn.createStatement();
            ResultSet rs = st.executeQuery(sqlQuerry);
            return rs;
        } finally {
        }
    }

    CallableStatement addPara(String ProcName, ArrayList param) throws ClassNotFoundException, SQLException {
        if (cnn == null || cnn.isClosed()) {
            openConnection();
        }
        CallableStatement cs;
        if (param != null) {
            String call = "{call " + ProcName + "(";
            for (Object o : param) {
                call += "?,";
            }
            call = call.substring(0, call.length() - 1);

            call += ")}";
            cs = cnn.prepareCall(call);
            for (int i = 0; i < param.size(); i++) {
                cs.setObject(i + 1, param.get(i));
            }
        }else {
        String call = "{call " + ProcName + "}";
        cs = cnn.prepareCall(call);
        }

        return cs;

    }

    public ResultSet executeQuery(String ProcName, ArrayList param) throws ClassNotFoundException, SQLException {

        CallableStatement cs = addPara(ProcName, param);
        return cs.executeQuery();
    }

    public int executeUpdateQuery(String ProcName, ArrayList param) throws ClassNotFoundException, SQLException {
        try {
            CallableStatement cs = addPara(ProcName, param);
            return cs.executeUpdate();
        } finally {
            closeConnection();
        }
    }

    public void closeConnection() {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException ex) {
            }
        }
    }

    public Connection getConnection() throws ClassNotFoundException, SQLException {
        if (cnn == null) {
            openConnection();
        }
        return cnn;
    }
}
